drop table gallery;

create table gallery(
	gallery_no number primary key,
	title varchar2(100), 
	content varchar2(2000), 
	category varchar2(20) not null, 
	wow number not null,	
	w_date date not null,
	count number not null, 
	org_imgname varchar2(200) not null,
	thumb_imgname varchar2(250) not null, 
	m_uid number not null,
	constraint fk_gallery_id foreign key(m_uid) references member(m_uid)
);

drop sequence gallery_seq;
create sequence gallery_seq nocache;


select * from MEMBER;
select * from gallery;

select gallery_no, title, content, wow, category, w_date, count, org_imgname, thumb_imgname, m_uid, m_nickname from(
		select gallery_no, title, content, wow,category, w_date, count, org_imgname, thumb_imgname,m_uid, m_nickname, ceil(rownum/5) as page from(
			select gallery_no, title, content, wow,category, to_char(w_date,'YYYY.MM.DD HH24:MI:SS') as w_date, count, org_imgname, thumb_imgname, g.m_uid as m_uid, m.m_nickname as m_nickname
			from gallery g, member m
			where g.m_uid=m.m_uid and g.m_uid=3 order by gallery_no desc
			)
		)where page=1
		
select count(*) from gallery g, member m where g.m_uid=m.m_uid and g.m_uid=3

update gallery set category='practice' where category='paractice'


drop table wow
create table wow(
	m_uid number not null,
	gallery_no number not null,
	constraint fk_wow_id foreign key(m_uid) references member(m_uid),
						 foreign key(gallery_no) references gallery(gallery_no),
						 unique(m_uid,gallery_no)
)

select gallery_no,title,wow,content,w_date,count,category,m_uid,org_imgname,thumb_imgname from(
	select gallery_no,title,wow,content,to_char(w_date,'YYYY-MM-DD') as w_date,count,category,m_uid,org_imgname,thumb_imgname
	from gallery
	where category='practice' and w_date between to_char(next_day(sysdate,1)-6,'yyyymmdd') and to_char(next_day(sysdate,2),'yyyymmdd')
	order by wow desc
)WHERE ROWNUM BETWEEN 1 AND 10


select gallery_no,title,wow,content,to_char(w_date,'YYYY-MM-DD') as w_date,count,category,m_uid,org_imgname,thumb_imgname
	from gallery 
	where w_date between to_char(next_day(sysdate,1)-6,'YYYY-MM-DD') and to_char(next_day(sysdate,2),'YYYY-MM-DD')
	
select to_char((next_day(sysdate,1)-13),'YYYY-MM-DD') from dual
select to_char(next_day(sysdate,1)-7,'YYYY-MM-DD') from dual
select sysdate from dual where sysdate between to_char(next_day(sysdate,1)-6,'yyyymmdd') and to_char(next_day(sysdate,2),'yyyymmdd')



/*
 * 주간 게시물 리스트
 */
-- 테이블
drop table best_weekly
create table best_weekly(
	best_pri_week number primary key, 
	year number not null, 
	month number not null, 
	week number not null,
	s_day varchar2(20) not null,
	l_day varchar2(20) not null
);


-- 베스트게시물 등록
<insert id="" parameterClass="map">

		<selectKey keyProperty="week" resultClass="string">
			select to_char(w_date,'ww') from gallery 	where gallery_no=5
		</selectKey>

	insert into wow(m_uid,gallery_no,week,wow) values(#m_uid#,#gallery_no#,#week#,#wow#)

</insert>

-- 베스트게시물 불러오기

<select id="totalContent" resultClass="int" parameterClass="GalleryVO">

select g.gallery_no, g.title, g.content, to_char(g.w_date,'yyyymmdd'), g.count, g.category, g.m_uid, g.org_imgname,g.thumb_imgname, w.wow

from gallery g,best_weekly w

where g.gallery_no=w.gallery_no and w.week=#value#

</select>

----------------------------------------------------------------------------------------------------------------------

select to_char(w_date,'ww'),to_char(w_date,'yyyymmdd') -- 통합 몇주차인지 일요일부터 시작
from gallery 



select to_char(w_date,'ww'),to_char(w_date,'yyyymmdd'),gallery_no,title,count
from gallery 
where to_char(w_date,'ww')=51 -- 해당 총 주차에 해당하는 게시물을가져옴



select to_char(w_date,'ww') as week,to_char(w_date,'yyyymmdd'),gallery_no,title,count
from gallery 
where gallery_no=27 -- 번호로 gallery 정보를 가져옴



insert into best_weekly(m_uid,gallery_no,week,wow) values(2,27,'51',7)

select * from best_weekly

-- step1
select w.week,
	   g.gallery_no,
	   g.title,
	   g.content,
	   to_char(g.w_date,'yyyymmdd'),
	   g.count,
	   g.category,
	   g.m_uid,
	   g.org_imgname,g.thumb_imgname,
	   w.wow
from gallery g,best_weekly w
where g.gallery_no=w.gallery_no and w.week='51'



------------test용 weekly data -------
select * from best_weekly

insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,1,1,20120101,20120107);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,1,2,20120108,20120114);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,1,3,20120115,20120121);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,1,4,20120122,20120128);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,1,5,20120129,20120204);

insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,2,1,20120205,20120211);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,2,2,20120212,20120218);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,2,3,20120219,20120225);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,2,4,20120215,20120303);

insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,3,1,20120304,20120310);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,3,2,20120310,20120317);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,3,3,20120318,20120324);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,3,4,20120325,20120331);

insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,4,1,20120401,20120407);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,4,2,20120408,20120414);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,4,3,20120415,20120421);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,4,4,20120422,20120428);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,4,5,20120429,20120505);

insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,5,1,20120506,20120512);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,5,2,20120513,20120519);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,5,3,20120520,20120526);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,5,4,20120527,20120602);

insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,6,1,20120603,20120609);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,6,2,20120610,20120616);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,6,3,20120617,20120623);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,6,4,20120624,20120630);

insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,7,1,20120701,20120707);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,7,2,20120708,20120714);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,7,3,20120715,20120721);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,7,4,20120722,20120728);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,7,5,20120729,20120804);

insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,8,1,20120805,20120811);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,8,2,20120812,20120818);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,8,3,20120819,20120825);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,8,4,20120826,20120901);

insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,9,1,20120902,20120908);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,9,2,20120909,20120915);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,9,3,20120916,20120922);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,9,4,20120923,20120929);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,9,5,20120930,20121006);

insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,10,1,20121007,20121013);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,10,2,20121014,20121020);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,10,3,20121021,20121027);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,10,4,20121028,20121103);

insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,11,1,20121104,20121110);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,11,2,20121111,20121117);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,11,3,20121118,20121124);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,11,4,20121125,20121201);

insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,12,1,20121202,20121208);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,12,2,20121209,20121215);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,12,3,20121216,20121222);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,12,4,20121223,20121229);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,12,5,20121230,20130105);

select * from best_weekly
select * from gallery

select * from member
delete from member where m_uid=10
select m_id, m_nickname,m_uId, m_level from member
where m_nickname='아이온'